create database TestDB
on
(
   name='TestDB',
   filename='E:\数据库文件\数据库跟目录文件.mdf',
   size=5mb,
   maxsize=100mb,
   filegrowth=10mb
)

log on
(
   name='TestDB_log',
   filename='E:\数据库文件\数据库跟目录文件_log.ldf',
   size=5mb,
   maxsize=100mb,
   filegrowth=10%
)
use TestDB
go

-- 1、
create table typeInfo
(
   typeId  int primary key identity(1,1),
   typeName varchar(10) not null,
)

create table loginInfo 
(
  LoginId int primary key identity(1,1),
  LoginName  varchar(10) not null unique,
  LoginPwd varchar(20) not null default('1,2,3,4,5,6'),
  Logsex char(1),
  Logbrithday datetime,
  Loghuiyuan  varchar
)

-- 2、
create database company
on
(
  name='company',
  filename='E:\数据库文件\数据库跟目录文件\company.mdf',
  size=5mb,
  maxsize=50mb,
  filegrowth=10%
)

log on
(
  name='company_log',
  filename='E:\数据库文件\数据库跟目录文件\company_log.mdf',
  size=5mb,
  maxsize=50mb,
  filegrowth=10%
)
use company
go

create table sectionInfo
(
   sectionID  int primary key,
   sectionName  varchar(10) not null
)
create table userInfo
(
  userNo  int primary key not null,
   userName  varchar(10) unique not null check(userName>4),
    userSex   varchar(2) not null check(userSex='男' or userSex='女' ),
    userAge   int  not null check(userAge>=1 or userAge<=100),
	userAddress  varchar(50) default('湖北'),
	userSection  int foreign key references sectionInfo(sectionID)
)
create table workInfos
(
   workId int primary key not null,
   userId  int foreign key references userInfo(userNo),
   workTime datetime not null,
   workDescription varchar(40)  not null check(workDescription='迟到'or 
   workDescription='早退'or workDescription='矿工' or workDescription='病假'or 
   workDescription='事假')
)

-- 3、
create database systems
on
(
  name='system',
  filename='E:\数据库文件\数据库跟目录文件\system.mdf',
  size=5mb,
  maxsize=50mb,
  filegrowth=10%
)
log on
(
  name='system_log',
  filename='E:\数据库文件\数据库跟目录文件\system_log.ldf',
  size=5mb,
  maxsize=50mb,
  filegrowth=10%
)
use systems
go
create table Classinfo
(
  classid int primary key,
  classname varchar(10) not null unique,
  classremark ntext
)
create table studentinfo
(
 stunum int primary key,
 stuname varchar check (len(stuname)>2) unique,
 stusex  varchar(2) default('男') check(stusex='男' or stusex='女'),
 stuage  int check(stuage>=15 or stuage<=40) not null,
 stuaddress nvarchar(50) default('湖北武汉'),
 stuclassid int references Classinfo(classid)
)
create table information
(
 number  int primary key,
 kechenname varchar unique not null,
 miaoshu  ntext
)
create table score
(
  scorenum int  primary key,
  scorestuid int references studentinfo(stunum) not null,
  scoreid int references information(number) not null,
  score int check(score>=0 or score<=100)
)
--4、
create database Home
on
(
 name='Home',
  filename='E:\数据库文件\数据库跟目录文件\Home.mdf',
  size=5mb,
  maxsize=50mb,
  filegrowth=10%
)
log on
(
  name='Home_log',
  filename='E:\数据库文件\数据库跟目录文件\Home_log.ldf',
  size=5mb,
  maxsize=50mb,
  filegrowth=10%
)
use Home
go

create table tblUser 
(
 userId int primary key identity(1,1),
 userName varchar,
 userTel  nvarchar(10)
)
create table tblHouseType 
(
 typeId  int primary key identity(1,1),
 typName varchar check(typName='别墅'or typName='普通住宅'or typName='平房' or typName='地下室') not null
)
create table tblQx 
(
 qxId int primary key identity(1,1),
 qxName varchar check(qxName='武昌'or qxName='汉阳' or qxName='汉口') not null
)

create table tblHouseInfo
(
  id int primary key identity(1,1),
  userId int foreign key references tblUser (userId),
  zj money,
  shi varchar,
  ting int,
  typeId int foreign key references tblHouseType (typeId),
  qxId int foreign key references tblQx (qxId)
)